const connection = require('../app/database')
class Userservice {
  async getUserByName(id) {
    const statement =`SELECT * FROM user WHERE name = ? `
    //私信查询 语句
    // ` SELECT u.id,u.name,u.avatar_url url , JSON_ARRAYAGG(JSON_OBJECT('msg', p.msg, 'id',user.id,'name',user.name,'url',user.avatar_url) ) pvmsginfo  from user u 
    // INNER JOIN pvmsg p ON p.u_id = u.id 
    // LEFT JOIN user  on p.s_id = user.id
    // GROUP BY u.id HAVING u.name = ? `
    
      const res = await connection.execute(statement, [id])
    return res
  }
  async create(name,password) {
    const statement = `INSERT INTO user(name,password) VALUES (?,?)`
    const res = await connection.execute(statement, [name, password])
    return res
  }

  async getwspvmsg(userid) {
    const statement =
    `SELECT u.id,u.name,u.avatar_url url , JSON_ARRAYAGG(JSON_OBJECT('msg', p.privatemessage, 'id',user.id,'name',user.name,'url',user.avatar_url) ) pvmsginfo  from user u 
        INNER JOIN pvmessage p ON p.userid = u.id 
        LEFT JOIN user  on p.userid = user.id WHERE p.type = 1
        GROUP BY u.id HAVING u.id = ?`
    const res = await connection.execute(statement,[userid])
    return res
  }
  async getpvmsg(userid) {
    const statement =
    `SELECT u.id,u.name,u.avatar_url url , JSON_ARRAYAGG(JSON_OBJECT('msg', p.privatemessage, 'id',user.id,'name',user.name,'url',user.avatar_url) ) pvmsginfo  from user u 
        INNER JOIN pvmessage p ON p.userid = u.id 
        LEFT JOIN user  on p.userid = user.id  WHERE p.type = 0
        GROUP BY u.id HAVING u.id = ?`
    const res = await connection.execute(statement,[userid])
    return res
  }
  
}
module.exports = new Userservice()